package entradaSaida;

import java.io.File;
import java.util.Iterator;
import java.util.Map.Entry;
import java.util.TreeMap;

import simulacao.Histograma;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class EscritorPlanilha extends EscritorArquivo {
	
	WritableWorkbook planilha;
	Workbook template;
	WritableSheet sheet;
	
	public EscritorPlanilha(boolean novaPlanilha) throws Exception{
		this.extensaoArquivo = ".xls";
		setCaminhoArquivo(escolherCaminhoArquivo(true)+"\\output.xls");
		if(novaPlanilha) criarNovaPlanilha();
		else copiarPlanilhaExistente("Template");
	}
	
	public EscritorPlanilha() throws Exception{
		this.extensaoArquivo = ".xls";
	}
	
	public static int coluna(String str){
		str = str.toUpperCase();
		int baseValue = 'A'-1;
		int value = 0, counter = 0;
		char[] word = str.toCharArray();
		for (char c : word) {
			value = counter + c-baseValue;
			counter = value * 26;
		}
		return value - 1;
	}
	
	public void criarNovaPlanilha() throws Exception{
		this.planilha = Workbook.createWorkbook(new File(getCaminhoArquivo()));
		this.sheet = this.planilha.createSheet("Primeira Folha", 0);
		this.planilha.write();
	}
	
	public void copiarPlanilhaExistente(String modelo) throws Exception{
		this.template = Workbook.getWorkbook(new File("rsc//"+modelo+".xls"));
		this.planilha = Workbook.createWorkbook(new File(caminhoArquivo),template);
		this.sheet = this.planilha.getSheet(0);
	}

	@Override
	public void escreverNoArquivo(String s) throws Exception{
		//copiarPlanilhaExistente();
		String[] dados = s.split("#");
		int qtdSimul = Integer.parseInt(dados[0]);
		preencherSerieDados(dados[1], coluna("A"), 5, sheet, qtdSimul);
		preencherSerieDados(dados[2], coluna("D"), 5, sheet, qtdSimul);
		this.planilha.write();
	}
	
	@Override
	public void escreverCabecalho(double p, double taxa, int qtdSimul, int retaliacao, String[] dados) throws Exception {
		Number pCell = new Number(coluna("D"), 0, p);
		Number taxaCell = new Number(coluna("B"), 0, taxa);
		Number qtdSimluCell = new Number(coluna("F"), 0, qtdSimul);
		Label retaliacaoLabel;
		if(retaliacao>=0) retaliacaoLabel = new Label(coluna("B"), 1, retaliacao + " rodadas");
		else retaliacaoLabel = new Label(coluna("B"), 1, "permanente");
		this.sheet.addCell(taxaCell);
		this.sheet.addCell(pCell);
		this.sheet.addCell(qtdSimluCell);
		this.sheet.addCell(retaliacaoLabel);
	}
	
	private void preencherSerieDados (String serieDados, int colunaInicial, int linhaInicial, WritableSheet sheet, int qtdSimul) throws Exception{
		String[] dados = serieDados.split("\n");
		int linhaAtual = linhaInicial;
		double payoff, frequencia, media =0.0, cdf = 0.0;
		for (String linha : dados) {
			String[] valores = linha.split("\t");
			payoff = Double.parseDouble(valores[0]);
			frequencia = Double.parseDouble(valores[1])/qtdSimul;
			media+= payoff*frequencia;
			cdf += frequencia;
			Number payoffCell = new Number(colunaInicial, linhaAtual, payoff);
			Number freqCell = new Number(colunaInicial+1, linhaAtual, frequencia);
			Number cdfCell = new Number(colunaInicial+2, linhaAtual, cdf);
			sheet.addCell(payoffCell);
			sheet.addCell(freqCell);
			sheet.addCell(cdfCell);
			linhaAtual++;
		}
		Number mediaCell = new Number(colunaInicial+2,linhaInicial-2,media);
		sheet.addCell(mediaCell);
	}
	
	public void preencherSerieDados (Histograma histograma, String colunaInicialStr, int linhaInicial, int qtdSimul) throws Exception{
		int linhaAtual = linhaInicial;
		int colunaInicial = coluna(colunaInicialStr);
		double payoff, frequencia, media =0.0, cdf = 0.0;
		TreeMap<Double, Integer> mapa = histograma.getMapaFrequencia();
		for (Iterator<Entry<Double, Integer>> iterator = mapa.entrySet().iterator(); iterator.hasNext();) {
			Entry<Double, Integer> entrada = (Entry<Double, Integer>) iterator.next();
			payoff = entrada.getKey();
			frequencia = ((double)entrada.getValue()/qtdSimul);
			media+= payoff*frequencia;
			cdf += frequencia;
			Number payoffCell = new Number(colunaInicial, linhaAtual, payoff);
			Number freqCell = new Number(colunaInicial+1, linhaAtual, frequencia);
			Number cdfCell = new Number(colunaInicial+2, linhaAtual, cdf);
			sheet.addCell(payoffCell);
			sheet.addCell(freqCell);
			sheet.addCell(cdfCell);
			linhaAtual++;
		}
		Number mediaCell = new Number(colunaInicial+2,linhaInicial-2,media);
		sheet.addCell(mediaCell);
	}
	
	@Override
	public void fecharArquivo() throws Exception{
		planilha.write();
		this.planilha.close();
	}
	
	public void escreverNoArquivo(double valor, int sheet, int linha, String coluna){
		WritableSheet ws = planilha.getSheet(sheet);
		Number number = new Number(coluna(coluna), linha, valor);
		try {
			ws.addCell(number);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void escreverNoArquivo(String string, int sheet, int linha, String coluna) {
		WritableSheet ws = planilha.getSheet(sheet);
		Label label = new Label(coluna(coluna), linha, string);
		try {
			ws.addCell(label);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}
